package com.luna.dao;

import com.luna.beans.User;
import com.luna.utils.JDBC;

import java.beans.Expression;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDao {

    public User selectByUserId(int user_id) throws Exception {
        User user = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        Connection con = null;
        String sql = "select * from user where id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, user_id);
            rs = ps.executeQuery();
            if (rs.next()) {
                user = new User(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("nickname"),
                        rs.getInt("state")
                );
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return user;
    }

    public User selectByUserNameAndPassword(String name, String password) throws Exception {
        User user = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        Connection con = null;
        String sql = "select * from user where name=? and password=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, password);
            rs = ps.executeQuery();
            if (rs.next()) {
                user = new User(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("nickname"),
                        rs.getInt("state")
                );
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return user;
    }

    public List<User> selectAllUser() throws Exception {
        List<User> list = new ArrayList<User>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from user";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new User(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("nickname"),
                        rs.getInt("state")
                ));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return list;
    }

    public List<User> selectAllNormalUser() throws Exception {
        List<User> list = new ArrayList<User>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from user where state in (0, -1)";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new User(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("nickname"),
                        rs.getInt("state")
                ));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return list;
    }

    public List<User> selectAllRestrictedUser() throws Exception {
        List<User> list = new ArrayList<User>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select * from user where state=-1";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new User(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("nickname"),
                        rs.getInt("state")
                ));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(rs, ps, con);
        }
        return list;
    }

    public void insertNormalUser(User user) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "insert into user(id, name, nickname, password, state) value(NULL, ?, ?, ?, 0)";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, user.getName());
            ps.setString(2, user.getNickname());
            ps.setString(3, user.getPassword());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(null, ps, con);
        }
    }

    public void dropUserById(int user_id) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "delete from user where id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, user_id);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(null, ps, con);
        }
    }

    public void restrictedUserById(int user_id) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "update user set state=-1 where id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, user_id);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(null, ps, con);
        }
    }

    public void updatePassword(String username, String password, String newPassword) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "update user set password=? where name=? and password=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setString(1, newPassword);
            ps.setString(2, username);
            ps.setString(3, password);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(null, ps, con);
        }
    }

    public void restrictUser(int user_id) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "update user set state=-1 where id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, user_id);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(null, ps, con);
        }
    }

    public void unRestrictUser(int user_id) throws Exception {
        Connection con = null;
        PreparedStatement ps = null;
        String sql = "update user set state=0 where id=?";
        try {
            con = JDBC.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, user_id);
            ps.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JDBC.close(null, ps, con);
        }
    }
}
